﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WinHBM.Core.DB
{
    class SQLiteHelper2
    {
        string path = System.Environment.CurrentDirectory + @"/Data/";
        string dbName = "WinHBM.sqlite";
        static SQLiteCommand cmd;
        static SQLiteDataReader sdr;
        SQLiteConnection cn;
        public SQLiteHelper2() {
             
        }
        //创建数据库文件
        public  void CreateDBFile()
          {
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            string databaseFileName = path + dbName;
            if (!File.Exists(databaseFileName))
            {
                try
                {
                    SQLiteConnection.CreateFile(databaseFileName);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
            }
            initConnection();
        }
        public  void initConnection()
        {
            if (cn == null) {
                cn = new SQLiteConnection("data source=" + path + dbName);
                cn.Open();
            }
        }
        
        public int insertData(String tableName,Dictionary<String,String> dic)
        {
            string sql = "insert into "+ tableName+" (";
            foreach (var item in dic)
            {
                sql += item.Key+",";
            }
            sql = sql.Substring(0,sql.Length-1);
            sql +=") values (";
            foreach (var item in dic)
            {
                sql += "@"+item.Key + ",";
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += ")";
            Console.WriteLine(sql);
            SQLiteParameter[] paras = new SQLiteParameter[dic.Count];
            int i = 0;
            foreach (var item in dic)
            {
                paras[i] = new SQLiteParameter("@"+ item.Key, item.Value);
                i++;
            }
            return ExecuteNonQuery(sql, paras);
        }
        public List<Dictionary<string, string>> selectData(String tableName, Dictionary<String, String> dic)
        {
            string sql = "select * from " + tableName;
            List<Dictionary<string, string>> list=new List<Dictionary<string, string>>();
            if (dic != null && dic.Count > 0)
            {
                SQLiteParameter[] paras = new SQLiteParameter[dic.Count];
                sql += " where ";
                int i = 0;
                foreach (var item in dic)
                {
                    sql += item.Key + "=@" + item.Key + " and ";
                    paras[i] = new SQLiteParameter("@"+ item.Key, item.Value);
                    i++;
                }
                sql = sql.Substring(0, sql.Length - 4);
                Console.WriteLine("SQL :"+sql);
                list=ExecuteQuery(sql,paras);
            }
            return list;
        }


        public Dictionary<string,string> findById(int id)
        {
            return null;
        }
    


        public int updateData(String tableName, Dictionary<String, String> dic, Dictionary<String, String> operation)
        {
            //UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
            string sql = "update " + tableName+" set ";
            int i = 0;
            SQLiteParameter[] paras = new SQLiteParameter[dic.Count];
            foreach (var item in dic)
            {
                sql += item.Key + "=@" + item.Key + " ,";
                paras[i] = new SQLiteParameter("@" + item.Key, item.Value);
                i++;
            }
            return 0;
        }
        public int deleteData(String tableName, Dictionary<String, String> dic)
        {
            //DELETE FROM Person WHERE LastName = 'Wilson'
            string sql = "delete from "+ tableName+" where ";
            int i = 0;
            SQLiteParameter[] paras = new SQLiteParameter[dic.Count];
            foreach (var item in dic)
            {
                sql += item.Key + "=@" + item.Key + " and ";
                paras[i] = new SQLiteParameter("@" + item.Key, item.Value);
                i++;
            }
            sql = sql.Substring(0, sql.Length - 4);
            Console.WriteLine("SQL :" + sql);
            return ExecuteNonQuery(sql, paras);
        }
        ///
        /// 该方法执行传入的增删改SQL语句
        /// 要执行的增删改SQL语句
        /// 返回更新的记录数
        public int ExecuteNonQuery(string sql)
        {
            int res;
            try
            {
                if (cn.State == ConnectionState.Closed)
                {
                    cn.Open();
                }
                cmd = new SQLiteCommand(sql, cn);
                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                res = -1;
                throw ex;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
            }
            return res;
        }
        ///
        /// 执行带参数的SQL增删改语句
        ///
        /// SQL增删改语句
        /// 参数集合
        /// 返回更新的记录数
        public int ExecuteNonQuery(string sql, SQLiteParameter[] paras)
        {
            int res;
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            using (cmd = new SQLiteCommand(sql, cn))
            {
                cmd.Parameters.AddRange(paras);
                res = cmd.ExecuteNonQuery();
            }
            return res;
        }
        ///
        /// 执行带参数的SQL查询语句
        ///
        /// SQL查询语句
        /// 参数集合
        /// 返回数据集合
        public List<Dictionary<string, string>> ExecuteQuery(string sql, SQLiteParameter[] paras)
        {
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            DataTable dt = new DataTable();
            cmd = new SQLiteCommand(sql, cn);
            cmd.Parameters.AddRange(paras);
            SQLiteDataReader sr = cmd.ExecuteReader();
            
            List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
            while (sr.Read())
            {
                Dictionary<string, string> dic = new Dictionary<string, string>();
                for (int i = 0; i < sr.FieldCount; i++)
                {
                    dic.Add(sr.GetName(i), sr[i].ToString());
                }
                list.Add(dic);
            }
            cn.Close();
            return list;
        }
        //创建表
        public  void CreateTable()
        {
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = cn;
                cmd.CommandText = "CREATE TABLE IF NOT EXISTS babyInfo(id,babyId int,classId int,name varchar,avatar varchar,flowerNum int)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "CREATE TABLE IF NOT EXISTS flowerLog(id,teacherId int,babyId int,time varchar,type varchar)";
                cmd.ExecuteNonQuery();
                cn.Close();
        }
        //---删除表
         void DeleteTable(String tableName)
        {
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = cn;
                cmd.CommandText = "DROP TABLE IF EXISTS "+tableName;
                cmd.ExecuteNonQuery();
                cn.Close();
        }
    }
}
